# 定义数据库工具类
import pymysql


class DBTools(object):

    # 创建连接-类方法，可以直接使用类名调用
    @classmethod
    def __create_conn(cls):    #私有方法前面加下划线
        conn = pymysql.connect(host="localhost", port=3306, user="root", password="123456",
                               database="test_db", charset="utf8")
        return conn  #不能遗漏

    # 查一条记录
    @classmethod
    def query_one(cls,sql):
        my_cursor = None
        my_conn = None
        res = None
        try:
            # 创建连接，借助类名调用类方法create_conn
            my_conn = DBTools.__create_conn()
            #创建游标
            my_cursor = my_conn.cursor()
            #执行sql语句
            my_cursor.execute(sql)
            #提取一条记录
            res = my_cursor.fetchone()
        except Exception as err:
            print("查询失败：",str(err))
        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()
        return res


    # 增删改记录
    @classmethod
    def db_uid(cls,sql):
        my_conn = None
        my_cursor = None
        try:
            # 创建连接
            my_conn = DBTools.__create_conn()
            # 创建游标
            my_cursor = my_conn.cursor()
            # 执行sql语句
            my_cursor.execute(sql)
            print("Affected rows:", my_conn.affected_rows())
            my_conn.commit()
        except Exception as err:
            print("sql执行失败：",str(err))
        #     回滚事务
            my_conn.rollback()
        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()




if __name__ == '__main__':
    # result = DBTools.query_one("select id,title from t_book;")
    # print("查询结果为：",result)
    result = DBTools.db_uid("update t_book set `read`= 10 where title = '笑傲江湖';")